import copy
import json
from time import sleep
from  sim_send.send_step import step_1,step_2,step_3
import  login_info.login_info as login
import tools.login_op as login_op
from tools.get_sql import get_all_dict,get_small_dict,trim_num_full,trim_num_small,del_total
from tools.log_op import get_cookies_from_txt,get_table_desc,put_json
class river_table_info():
    def test(self):
        print(login.emr_user)
    def __init__(self):
        self.emr_user = login.emr_user
        self.emr_password = login.emr_password
        self.get_cookie_url = login.studio_get_cookie_url
        self.send_job_url = login.send_job_url
        self.chk_job_stats_url_head = login.chk_job_stats_url_head
        self.get_job_result_url_head = login.get_result_url
        self.cookies = self.get_cookies()

    def get_cookies(self):
        cookie = get_cookies_from_txt()
        if(cookie ==''):
            return  login_op.get_brower_cookies(self.get_cookie_url,self.emr_user,self.emr_password)
        return  cookie


    def run_emr_sql(self,sql):
        #step1 start
        print('----------------------------------')
        print('向服务器发送sql语句(step1):')
        print(sql)
        instance = step_1(self,sql)
        print('----------------------------------')
        #step_2 start
        print('\n----------------------------------')
        status = step_2(self,instance)
        while status != 'FINISHED':
            if status == 'ERROR':
                print('stp_2执行失败的sql是 ：',sql)
                return
            print('等待3秒')
            sleep(3)
            status = step_2(self,instance)
        print('step2结束')
        print('----------------------------------')
        #step3 start
        print('\n----------------------------------')
        print('接受服务器返回信息(step3):')
        result = step_3(self,instance)
        print('----------------------------------')
        return result

    def get_rows(self,table_name=''):
        if(table_name==''):
            print('输入表名:')
            table_name=input('')
        self.table_name = table_name
        desc_sql = 'desc '+table_name
        result = self.run_emr_sql(desc_sql)
        detail = result['retData']['detail']
        if_success = detail[1:6]

        if(if_success!=' 运行成功'):
            raise Exception('获取',self.table_name,'列信息失败')
        print('获取',self.table_name,'列信息成功')

        rows = result['retData']['stmtResults'][0]['sqlResult']['rows']
        return rows
    def drop_table(self):
        print('输入表名:')
        sql = input('')
        self.table_name = sql
        desc_sql = 'drop table ' + sql
        print(desc_sql)
        result = self.run_emr_sql(desc_sql)
        #可以复写
        detail = result['retData']['detail']
        if_success = detail[1:6]
        print(if_success)
        if (if_success != ' 运行成功'):
            raise Exception('运行失败')
        rows = result['retData']['stmtResults'][0]['sqlResult']['rows']
        return rows
    def get_columns(self,list_s):
        # 外部的壳
        table_dict = json.loads(get_table_desc())
        #内部元素
        dict={}
        for x in list_s:
            print('数据类型:',x['data_type'],'--','列名',x['col_name'],'--','备注:',x['comment'])
            dict[x['col_name']] = {'data_type':x['data_type'],"comment":x['comment'],"col_name":x['col_name']}
            table_dict[self.table_name] = dict
        put_json(table_dict)
    def make_sql(self,name_list):

        sql_middle= '\t'+'(select count(1) as total from '+ self.table_name +')'+ ','+'\n'
        for index,col_name in enumerate(name_list):
            col = col_name['col_name']
            if(index != len(name_list)-1):
                sql_middle += str('\t'+ \
                                  '(select count(1) as {}_num from ' + self.table_name + ' where {} is not NULL)' + 'a' + str(index) + ','+'\n').format(col, col)
            else:
                sql_middle += str('\t'+ \
                                  '(select count(1) as {}_num from ' + self.table_name + ' where {} is not NULL)' + 'a' + str(index)).format(col, col)
        sql = 'select * from \n{}'.format(sql_middle)
        return sql



    def create_new_table(self):
        std_table = 'std'+self.table_name[3:]
        std_table_err = std_table+'_err'
        self.std = std_table
        self.err = std_table_err
        self.std_table = 'create table  {} like {}'.format(std_table,self.table_name)
        # print(self.std_table)
        self.err_table = 'create table  {} like {}'.format(std_table_err,self.table_name)
        # print(self.err_table)
    def win(self,sql,rows):

        rows_result = self.run_emr_sql(sql)
        detail = rows_result['retData']['detail']
        if_success = detail[1:6]
        # print(if_success)
        if (if_success != ' 运行成功'):
            raise Exception('运行失败')
        total = rows_result['retData']['stmtResults'][0]['sqlResult']['rows'][0]['total']
        # print('这里是total的总数：',total)
        if(total == '0'):
            self.create_new_table()
        else:
            self.create_new_table()
            result_dict = rows_result['retData']['stmtResults'][0]['sqlResult']['rows'][0]
            full_dict = copy.deepcopy(result_dict)
            small_dict = copy.deepcopy(result_dict)
            small_dict = get_small_dict(small_dict,total)
            full_dict = get_all_dict(full_dict,total)
            self.small = small_dict
            self.full = full_dict
            self.as_select(rows)

    def as_select(self,rows):
        # sql = "INSERT OVERWRITE table {} \n \t select * FROM (".format(self.std)
        sql = "INSERT OVERWRITE table {} \n \t select ".format(self.std)
        sql_middle='\n\t\t\tselect'
        for index,col_name in enumerate(rows):
            col = col_name['col_name']
            if(index != len(rows)-1):
                sql +=str(col+',')
            else:
                sql += str(col+' ')
        sql += '\n\t\tfrom ( '
        list_1 = list(trim_num_full(del_total(self.full)).keys())
        list_2 = list(trim_num_small(self.small).keys())
        for index,key in enumerate(list_1):
            sql_middle += '\n\t\t\t\t' + key + ','

        for index, key in enumerate(list_2):
            if (index != len(list_2) - 1):
                sql_middle += '\n\t\t\t\t' + 'CASE WHEN ' +key+ ' is null then ' + '\'待填写\' ' + 'else '+ key +' end as ' +key +','
            if (index == len(list_2) - 1):
                sql_middle += '\n\t\t\t\t' + 'CASE WHEN ' +key+ ' is null then ' + '\'待填写\' ' + 'else '+ key +' end as ' +key+' from '+self.table_name+'\n'

        # print(sql_middle)
        sql_end = ')t'
        end = sql + sql_middle + sql_end
        print(end)


if __name__ == '__main__':
    a = river_table_info()
    # a.drop_table()
    rows = a.get_rows('')
    a.get_columns(rows)
    # # # #查询出total的个数
    sql = a.make_sql(rows)
    a.win(sql,rows)
    get_table_desc()
